import mysql.connector
from mysql.connector import Error
from tkinter import *
from tkinter import ttk, messagebox, simpledialog
from tkcalendar import DateEntry

# 数据库连接配置
# DB_CONFIG = {
#     'host': 'localhost',
#     'user': 'root',
#     'password': '123456',
#     'database': 'gym_management'
# }


class GymManagementSystem:
    def __init__(self, root):
        self.root = root
        self.root.title("健身房管理系统")
        self.root.geometry("1280x1024")
        self.root.minsize(1280, 1024)

        # 创建数据库连接
        self.conn = self.create_database_connection()
        if not self.conn:
            messagebox.showerror("错误", "无法连接到数据库，请检查配置")
            root.destroy()
            return

        # 创建主框架
        self.main_frame = Frame(self.root)
        self.main_frame.pack(fill=BOTH, expand=True, padx=10, pady=10)

        # 创建侧边菜单
        self.create_side_menu()

        # 创建内容区域
        self.content_frame = Frame(self.main_frame)
        self.content_frame.pack(side=RIGHT, fill=BOTH, expand=True)

        # 初始显示欢迎界面
        self.show_welcome_screen()

    def create_database_connection(self):
        """创建数据库连接"""
        try:
            conn = mysql.connector.connect(**{'host': 'localhost','user': 'root','password': '123456','database': 'gym_management'})
            if conn.is_connected():
                return conn
        except Error as e:
            print(f"连接数据库时出错: {e}")
        return None

    def create_side_menu(self):
        """创建侧边菜单"""
        self.side_menu = Frame(self.main_frame, width=200, bg="#f0f0f0")
        self.side_menu.pack(side=LEFT, fill=Y)

        # 菜单标题
        Label(self.side_menu, text="健身房管理系统", font=("Arial", 14, "bold"),
              bg="#f0f0f0", pady=10).pack()

        # 菜单按钮
        menu_items = [
            ("欢迎界面", self.show_welcome_screen),
            ("用户管理", self.show_user_management),
            ("会员管理", self.show_member_management),
            ("教练管理", self.show_coach_management),
            ("课程管理", self.show_course_management),
            ("选课记录", self.show_course_selection),
            ("会员套餐", self.show_membership_package),
            ("器材管理", self.show_equipment_management),
            ("权限管理", self.show_permission_management),
            ("训练记录", self.show_training_record)
        ]

        for text, command in menu_items:
            btn = Button(self.side_menu, text=text, font=("Arial", 12),
                         command=command, width=20, height=2,
                         bg="#e0e0e0", activebackground="#d0d0d0",
                         relief=FLAT, bd=0)
            btn.pack(pady=5, padx=10)

    def clear_content_frame(self):
        """清空内容区域"""
        for widget in self.content_frame.winfo_children():
            widget.destroy()

    def show_welcome_screen(self):
        """显示欢迎界面"""
        self.clear_content_frame()

        frame = Frame(self.content_frame)
        frame.pack(fill=BOTH, expand=True, padx=20, pady=20)

        Label(frame, text="欢迎使用健身房管理系统", font=("Arial", 24, "bold"),
              pady=20).pack()

        Label(frame, text="本系统用于管理健身房的会员、教练、课程等信息",
              font=("Arial", 14), pady=10).pack()

        # 系统信息
        info_frame = Frame(frame)
        info_frame.pack(pady=20)

        # 统计数据
        stats_frame = Frame(frame)
        stats_frame.pack(fill=X, pady=20)

        # 功能说明
        func_frame = Frame(frame)
        func_frame.pack(fill=BOTH, expand=True)

        Label(func_frame, text="系统功能:", font=("Arial", 14, "bold"),
              anchor=W).pack(fill=X, pady=5)

        functions = [
            "用户管理：添加、编辑、删除用户信息，分配角色",
            "会员管理：管理会员资料、会员卡状态",
            "教练管理：管理教练信息、专业领域",
            "课程管理：创建课程、安排时间、设置容量",
            "选课记录：查看会员选课情况、处理退课",
            "会员套餐：管理各类会员套餐及价格",
            "器材管理：跟踪器材状态、维护记录",
            "权限管理：配置不同角色的操作权限",
            "训练记录：记录会员训练情况和课程签到"
        ]

        for func in functions:
            Label(func_frame, text="- " + func, font=("Arial", 12),
                  anchor=W).pack(fill=X, pady=2)

    # 用户管理界面
    def show_user_management(self):
        self.clear_content_frame()
        self.display_management_frame("User", "用户管理",
                                      ["user_id", "username", "role", "phone", "email"],
                                      ["username", "password", "role", "phone", "email"],
                                      ["管理", "教练", "会员"])

    # 会员管理界面
    def show_member_management(self):
        self.clear_content_frame()
        self.display_management_frame("Member", "会员管理",
                                      ["member_id", "name", "gender", "birthday",
                                       "card_start_date", "card_end_date", "card_status"],
                                      ["name", "gender", "birthday", "card_start_date",
                                       "card_end_date", "card_status"],
                                      ["男", "女", "其他"],
                                      foreign_key="member_id",
                                      foreign_table="User",
                                      foreign_columns=["user_id", "username"])

    # 教练管理界面
    def show_coach_management(self):
        self.clear_content_frame()
        self.display_management_frame("Coach", "教练管理",
                                      ["coach_id", "name", "specialty", "teaching_hours"],
                                      ["name", "specialty", "teaching_hours", "introduction"],
                                      None,
                                      foreign_key="coach_id",
                                      foreign_table="User",
                                      foreign_columns=["user_id", "username"])

    # 课程管理界面
    def show_course_management(self):
        self.clear_content_frame()
        self.display_management_frame("Course", "课程管理",
                                      ["course_id", "course_name", "class_hours",
                                       "coach_id", "class_time", "max_capacity", "current_count"],
                                      ["course_name", "class_hours", "coach_id", "class_time",
                                       "max_capacity"],
                                      None,
                                      foreign_key="coach_id",
                                      foreign_table="Coach",
                                      foreign_columns=["coach_id", "name"])

    # 选课记录界面
    def show_course_selection(self):
        self.clear_content_frame()
        self.display_management_frame("CourseSelection", "选课记录管理",
                                      ["selection_id", "member_id", "course_id",
                                       "selection_time", "withdraw_status", "comment"],
                                      ["member_id", "course_id", "withdraw_status", "comment"],
                                      [0, 1],
                                      foreign_key1="member_id",
                                      foreign_table1="Member",
                                      foreign_columns1=["member_id", "name"],
                                      foreign_key2="course_id",
                                      foreign_table2="Course",
                                      foreign_columns2=["course_id", "course_name"])

    # 会员套餐界面
    def show_membership_package(self):
        self.clear_content_frame()
        self.display_management_frame("MembershipPackage", "会员套餐管理",
                                      ["package_id", "package_name", "price", "valid_days"],
                                      ["package_name", "price", "valid_days", "description"],
                                      None)

    # 器材管理界面
    def show_equipment_management(self):
        self.clear_content_frame()
        self.display_management_frame("Equipment", "器材管理",
                                      ["equipment_id", "name", "status", "purchase_date"],
                                      ["name", "status", "purchase_date", "maintenance_log"],
                                      ["使用中", "维护中", "损坏"])

    # 权限管理界面
    def show_permission_management(self):
        self.clear_content_frame()
        self.display_management_frame("Permission", "权限管理",
                                      ["perm_id", "role", "module", "permission"],
                                      ["role", "module", "permission"],
                                      ["管理", "教练", "会员"])

    # 训练记录界面
    def show_training_record(self):
        self.clear_content_frame()
        self.display_management_frame("TrainingRecord", "训练记录管理",
                                      ["record_id", "member_id", "course_id",
                                       "checkin_time", "content"],
                                      ["member_id", "course_id", "content"],
                                      None,
                                      foreign_key1="member_id",
                                      foreign_table1="Member",
                                      foreign_columns1=["member_id", "name"],
                                      foreign_key2="course_id",
                                      foreign_table2="Course",
                                      foreign_columns2=["course_id", "course_name"])

    def display_management_frame(self, table_name, title, list_columns, form_columns,
                                 combo_values=None, foreign_key=None, foreign_table=None,
                                 foreign_columns=None, foreign_key1=None, foreign_table1=None,
                                 foreign_columns1=None, foreign_key2=None, foreign_table2=None,
                                 foreign_columns2=None):
        """显示通用管理界面"""
        frame = Frame(self.content_frame)
        frame.pack(fill=BOTH, expand=True, padx=20, pady=20)

        # 标题
        Label(frame, text=title, font=("Arial", 18, "bold"), pady=10).pack()

        # 搜索框
        search_frame = Frame(frame)
        search_frame.pack(fill=X, pady=10)

        search_var = StringVar()
        search_entry = Entry(search_frame, textvariable=search_var, font=("Arial", 12), width=50)
        search_entry.pack(side=LEFT, padx=(0, 10))

        def search_data():
            search_term = search_var.get().strip()
            self.load_data(table_name, list_columns, search_term)

        Button(search_frame, text="搜索", command=search_data,
               font=("Arial", 12)).pack(side=LEFT)

        # 刷新按钮
        Button(search_frame, text="刷新", command=lambda: self.load_data(table_name, list_columns),
               font=("Arial", 12)).pack(side=LEFT, padx=10)

        # 表格
        table_frame = Frame(frame)
        table_frame.pack(fill=BOTH, expand=True, pady=10)

        self.tree = ttk.Treeview(table_frame, columns=list_columns, show="headings", height=15)

        # 设置列宽
        for col in list_columns:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=100)

        # 滚动条
        scrollbar = ttk.Scrollbar(table_frame, orient="vertical", command=self.tree.yview)
        self.tree.configure(yscroll=scrollbar.set)
        scrollbar.pack(side=RIGHT, fill=Y)
        self.tree.pack(fill=BOTH, expand=True)

        # 绑定选择事件
        self.tree.bind("<<TreeviewSelect>>", lambda e: self.on_item_select(table_name, form_columns,
                                                                           combo_values, foreign_key,
                                                                           foreign_table, foreign_columns,
                                                                           foreign_key1, foreign_table1,
                                                                           foreign_columns1, foreign_key2,
                                                                           foreign_table2, foreign_columns2))

        # 加载数据
        self.load_data(table_name, list_columns)

        # 按钮框架
        btn_frame = Frame(frame)
        btn_frame.pack(fill=X, pady=10)

        Button(btn_frame, text="添加", command=lambda: self.open_form(table_name, "add", None,
                                                                      form_columns, combo_values,
                                                                      foreign_key, foreign_table, foreign_columns,
                                                                      foreign_key1, foreign_table1, foreign_columns1,
                                                                      foreign_key2, foreign_table2, foreign_columns2),
               font=("Arial", 12), width=10).pack(side=LEFT, padx=5)

        Button(btn_frame, text="编辑", command=lambda: self.open_form(table_name, "edit",
                                                                      self.get_selected_item(),
                                                                      form_columns, combo_values,
                                                                      foreign_key, foreign_table, foreign_columns,
                                                                      foreign_key1, foreign_table1, foreign_columns1,
                                                                      foreign_key2, foreign_table2, foreign_columns2),
               font=("Arial", 12), width=10).pack(side=LEFT, padx=5)

        Button(btn_frame, text="删除", command=lambda: self.delete_item(table_name,
                                                                        self.get_selected_item()),
               font=("Arial", 12), width=10).pack(side=LEFT, padx=5)

    def load_data(self, table_name, columns, search_term=""):
        """从数据库加载数据"""
        # 清空表格
        for item in self.tree.get_children():
            self.tree.delete(item)

        try:
            cursor = self.conn.cursor()

            if search_term:
                # 构建搜索查询
                query = f"SELECT {', '.join(columns)} FROM {table_name} WHERE "
                search_conditions = []
                for col in columns:
                    if col != "password":  # 避免搜索密码字段
                        search_conditions.append(f"{col} LIKE %s")
                query += " OR ".join(search_conditions)
                cursor.execute(query, (f"%{search_term}%",) * len(search_conditions))
            else:
                # 普通查询
                query = f"SELECT {', '.join(columns)} FROM {table_name}"
                cursor.execute(query)

            rows = cursor.fetchall()

            for row in rows:
                self.tree.insert("", END, values=row)

        except Error as e:
            messagebox.showerror("错误", f"加载数据时出错: {e}")
        finally:
            cursor.close()

    def get_selected_item(self):
        """获取选中的项目"""
        selected_items = self.tree.selection()
        if not selected_items:
            return None
        return self.tree.item(selected_items[0], "values")

    def on_item_select(self, table_name, form_columns, combo_values, foreign_key, foreign_table, foreign_columns,
                       foreign_key1, foreign_table1, foreign_columns1, foreign_key2, foreign_table2, foreign_columns2):
        """项目选择事件处理"""
        pass  # 由具体表单处理

    def open_form(self, table_name, action, item, form_columns, combo_values, foreign_key, foreign_table,
                  foreign_columns,
                  foreign_key1, foreign_table1, foreign_columns1, foreign_key2, foreign_table2, foreign_columns2):
        """打开表单窗口"""
        form_window = Toplevel(self.root)
        form_window.title(f"{'添加' if action == 'add' else '编辑'} {table_name}")
        form_window.geometry("600x400")
        form_window.resizable(False, False)

        frame = Frame(form_window, padx=20, pady=20)
        frame.pack(fill=BOTH, expand=True)

        entries = {}
        combos = {}
        date_entries = {}

        # 外键下拉框
        foreign_combo1 = None
        foreign_combo2 = None

        # 如果有第一个外键
        if foreign_key1 and foreign_table1 and foreign_columns1:
            try:
                cursor = self.conn.cursor()
                cursor.execute(f"SELECT {foreign_columns1[0]}, {foreign_columns1[1]} FROM {foreign_table1}")
                foreign_data1 = cursor.fetchall()
                cursor.close()

                foreign_frame1 = Frame(frame)
                foreign_frame1.pack(fill=X, pady=5)

                Label(foreign_frame1, text=f"{foreign_key1}:", width=15).pack(side=LEFT)
                foreign_combo1 = ttk.Combobox(foreign_frame1, width=40)
                foreign_combo1['values'] = [f"{item[0]} - {item[1]}" for item in foreign_data1]
                foreign_combo1.pack(side=RIGHT)
                entries[foreign_key1] = foreign_combo1
            except Error as e:
                messagebox.showerror("错误", f"加载外键数据时出错: {e}")

        # 如果有第二个外键
        if foreign_key2 and foreign_table2 and foreign_columns2:
            try:
                cursor = self.conn.cursor()
                cursor.execute(f"SELECT {foreign_columns2[0]}, {foreign_columns2[1]} FROM {foreign_table2}")
                foreign_data2 = cursor.fetchall()
                cursor.close()

                foreign_frame2 = Frame(frame)
                foreign_frame2.pack(fill=X, pady=5)

                Label(foreign_frame2, text=f"{foreign_key2}:", width=15).pack(side=LEFT)
                foreign_combo2 = ttk.Combobox(foreign_frame2, width=40)
                foreign_combo2['values'] = [f"{item[0]} - {item[1]}" for item in foreign_data2]
                foreign_combo2.pack(side=RIGHT)
                entries[foreign_key2] = foreign_combo2
            except Error as e:
                messagebox.showerror("错误", f"加载外键数据时出错: {e}")

        # 普通字段
        for i, col in enumerate(form_columns):
            if col == "password" and action == "add":
                # 密码字段
                row_frame = Frame(frame)
                row_frame.pack(fill=X, pady=5)

                Label(row_frame, text=f"{col}:", width=15).pack(side=LEFT)
                password_entry = Entry(row_frame, show="*", width=40)
                password_entry.pack(side=RIGHT)
                entries[col] = password_entry
            elif col in ["card_start_date", "card_end_date", "purchase_date", "class_time", "checkin_time",
                         "selection_time"]:
                # 日期字段
                row_frame = Frame(frame)
                row_frame.pack(fill=X, pady=5)

                Label(row_frame, text=f"{col}:", width=15).pack(side=LEFT)
                date_entry = DateEntry(row_frame, width=12, background='darkblue', foreground='white', borderwidth=2)
                date_entry.pack(side=RIGHT)
                date_entries[col] = date_entry
            elif combo_values and col in combo_values:
                # 下拉框字段
                row_frame = Frame(frame)
                row_frame.pack(fill=X, pady=5)

                Label(row_frame, text=f"{col}:", width=15).pack(side=LEFT)
                combo = ttk.Combobox(row_frame, values=combo_values, width=40)
                combo.pack(side=RIGHT)
                combos[col] = combo
            else:
                # 普通文本字段
                row_frame = Frame(frame)
                row_frame.pack(fill=X, pady=5)

                Label(row_frame, text=f"{col}:", width=15).pack(side=LEFT)
                entry = Entry(row_frame, width=40)
                entry.pack(side=RIGHT)
                entries[col] = entry

        # 填充编辑数据
        if action == "edit" and item:
            for i, col in enumerate(form_columns):
                if col in entries:
                    entries[col].insert(0, str(item[i]) if item[i] is not None else "")
                elif col in combos:
                    combos[col].set(str(item[i]) if item[i] is not None else "")
                elif col in date_entries:
                    date = item[i]
                    if date:
                        date_entries[col].set_date(date)
            # 外键处理
            if foreign_combo1 and foreign_key1 in item:
                for idx, data in enumerate(foreign_data1):
                    if data[0] == item[foreign_key1]:
                        foreign_combo1.current(idx)
                        break
            if foreign_combo2 and foreign_key2 in item:
                for idx, data in enumerate(foreign_data2):
                    if data[0] == item[foreign_key2]:
                        foreign_combo2.current(idx)
                        break

        # 按钮框架
        btn_frame = Frame(form_window)
        btn_frame.pack(fill=X, pady=10)

        def save_data():
            values = {}
            for col, entry in entries.items():
                values[col] = entry.get()
            for col, combo in combos.items():
                values[col] = combo.get()
            for col, date_entry in date_entries.items():
                values[col] = date_entry.get_date().strftime("%Y-%m-%d") if date_entry.get_date() else None

            # 外键值处理
            if foreign_combo1 and foreign_key1:
                if foreign_combo1.get():
                    values[foreign_key1] = foreign_combo1.get().split(" - ")[0]
            if foreign_combo2 and foreign_key2:
                if foreign_combo2.get():
                    values[foreign_key2] = foreign_combo2.get().split(" - ")[0]

            try:
                cursor = self.conn.cursor()

                if action == "add":
                    # 添加新记录
                    columns = ", ".join(values.keys())
                    placeholders = ", ".join(["%s"] * len(values))
                    query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
                    cursor.execute(query, tuple(values.values()))
                else:
                    # 编辑记录
                    updates = ", ".join([f"{col} = %s" for col in values.keys()])
                    primary_key = form_columns[0]  # 假设第一个字段是主键
                    query = f"UPDATE {table_name} SET {updates} WHERE {primary_key} = %s"
                    cursor.execute(query, tuple(values.values()) + (item[0],))

                self.conn.commit()
                messagebox.showinfo("成功", f"{table_name}记录已成功{'添加' if action == 'add' else '更新'}")
                form_window.destroy()
                self.load_data(table_name, form_columns[:1] + form_columns[2:])  # 重新加载数据
            except Error as e:
                self.conn.rollback()
                messagebox.showerror("错误", f"保存记录时出错: {e}")
            finally:
                cursor.close()

        Button(btn_frame, text="保存", command=save_data, font=("Arial", 12), width=10).pack(side=LEFT, padx=5,
                                                                                             anchor=CENTER)
        Button(btn_frame, text="取消", command=form_window.destroy, font=("Arial", 12), width=10).pack(side=RIGHT,
                                                                                                       padx=5,
                                                                                                       anchor=CENTER)

    def delete_item(self, table_name, item):
        """删除记录"""
        if not item:
            messagebox.showwarning("警告", "请先选择要删除的记录")
            return

        confirm = messagebox.askyesno("确认", f"确定要删除ID为{item[0]}的记录吗?")
        if not confirm:
            return

        try:
            cursor = self.conn.cursor()
            primary_key = item[0]
            query = f"DELETE FROM {table_name} WHERE {table_name}_id = %s"
            cursor.execute(query, (primary_key,))
            self.conn.commit()
            messagebox.showinfo("成功", f"记录已成功删除")
            self.load_data(table_name,
                           [f"{table_name}_id"] + [col for col in self.tree["columns"] if col != f"{table_name}_id"])
        except Error as e:
            self.conn.rollback()
            messagebox.showerror("错误", f"删除记录时出错: {e}")
        finally:
            cursor.close()


# 主程序入口
if __name__ == "__main__":
    root = Tk()
    app = GymManagementSystem(root)
    root.mainloop()